﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DalGen.Contracts;
using System.Xml;
using System.Data.SqlClient;
using System.Data;
using DalGen.Entity;
using NVelocity;
using System.IO;
using DalGen.Common;
using DalGen.Enum;

namespace DalGen.Service
{
    public class SqlBuilder : BuilderBase, IBuilder
    {
        protected SqlConnection _conn;

        public SqlBuilder()
        {
            string sSplit = System.AppDomain.CurrentDomain.BaseDirectory.Substring(System.AppDomain.CurrentDomain.BaseDirectory.Length - 1);
            string ConfigPath;
            if (sSplit != @"\" && sSplit != "/")
                ConfigPath = System.AppDomain.CurrentDomain.BaseDirectory + @"\";
            else
                ConfigPath = System.AppDomain.CurrentDomain.BaseDirectory;
            string sConfigFileName = ConfigPath + @"Reflection\SQL2DotNet.xml";
            domSql2DotNet = new XmlDocument();
            domSql2DotNet.Load(sConfigFileName);

        }

        public SqlConnection GetConn(string dataBaseName)
        {
            string connstr = "Data Source={0};DataBase={1};Password={2};User ID={3}";
            SqlConnection _sqlcon = new SqlConnection(string.Format(connstr, ProjectInfo.Server, dataBaseName, ProjectInfo.Pwd, ProjectInfo.UID));
            return _sqlcon;
        }

        public void DisconnectFromDB(SqlConnection conn)
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }


        public string ConnectionString { get; set; }

        public EProjectInfo ProjectInfo { get; set; }


        public List<string> GetTableName(string dbName)
        {
            SqlConnection _conn = GetConn(dbName);
			SqlCommand sqlComm = new SqlCommand("select name from sysobjects where type = 'U' order by name asc ", _conn);
            sqlComm.CommandType = CommandType.Text;
            SqlDataAdapter sdaTables = new SqlDataAdapter(sqlComm);
            DataTable dtTables = new DataTable();
            sdaTables.Fill(dtTables);
            DisconnectFromDB(_conn);

            List<string> tableName = new List<string>();

            foreach (DataRow dr in dtTables.Rows)
            {
                tableName.Add(dr["name"].ToString());
            }

            return tableName;
        }


        public List<string> GetDataBaseName()
        {
            SqlConnection _conn = GetConn("master");
            SqlCommand sqlComm = new SqlCommand("SELECT Name from Master..SysDatabases ORDER BY Name", _conn);
            sqlComm.CommandType = CommandType.Text;
            SqlDataAdapter sdaTables = new SqlDataAdapter(sqlComm);
            DataTable dtTables = new DataTable();
            sdaTables.Fill(dtTables);
            DisconnectFromDB(_conn);
            List<string> s = new List<string>();
            foreach (DataRow dr in dtTables.Rows)
            {
                s.Add(dr["Name"].ToString());
            }
            return s;
        }


        // 获取指定表的主键
        public DataTable GetPrimaryKeys(string sDataBaseName, string sTableName)
        {
            SqlConnection _conn = GetConn(sDataBaseName);

            DataTable dtPkey = new DataTable("PrimaryKeys");
            SqlCommand sqlComm = new SqlCommand("sp_pkeys", _conn);
            sqlComm.CommandType = CommandType.StoredProcedure;

            SqlParameter sqlParam = new SqlParameter();
            sqlParam.ParameterName = "@table_name";
            sqlParam.SqlDbType = SqlDbType.VarChar;
            sqlParam.Size = 200;
            sqlParam.Direction = ParameterDirection.Input;
            sqlParam.Value = sTableName;
            sqlComm.Parameters.Add(sqlParam);

            SqlDataAdapter sqlDpt = new SqlDataAdapter(sqlComm);
            sqlDpt.Fill(dtPkey);

            DisconnectFromDB(_conn);

            return dtPkey;
        }

        public List<ETableInfo> GetColumns(string dataBaseName, string sTableName)
        {
            #region 查询表基本信息
            //            string strSql = @"select 
            //[ColumnName]=[Columns].name,
            //[SystemTypeName]=[Types].name,
            //[Precision]=[Columns].precision,
            //[Scale]=[Columns].scale,
            //[MaxLength]=[Columns].max_length,
            //[IsNullable]=[Columns].is_nullable,
            //[IsRowGUIDCol]=[Columns].is_rowguidcol,
            //[IsIdentity]=[Columns].is_identity,
            //[IsComputed]=[Columns].is_computed,
            //[IsXmlDocument]=[Columns].is_xml_document,
            //[Description]=[Properties].value
            //from sys.tables as [Tables]
            //inner join sys.columns as [Columns]
            //on [Tables].object_id=[Columns].object_id
            //inner join sys.types as [Types]
            //on [Columns].system_type_id=[Types].system_type_id and is_user_defined=0 and [Types].name<>'sysname'
            //left outer join sys.extended_properties as [Properties]
            //on [Properties].major_id=[Tables].object_id and [Properties].minor_id=[Columns].column_id and [Properties].name='MS_Description'
            //where [Tables].name='" + sTableName + "' order by [Columns].column_id";
            #endregion

            #region sql
            string strSql = @"SELECT 
    TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
    TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
    ColumnID=C.column_id,
    ColumnName=C.name,
    PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
    [IDENTITY]= C.is_identity,
    Computed= C.is_computed,
    SystemTypeName=T.name,
    Length=C.max_length,
    Precision=C.precision,
    Scale=C.scale,
    NullAble=C.is_nullable,
    [Default]=ISNULL(D.definition,N''),
    [Description]=ISNULL(PFD.[value],N''),
    IndexName=ISNULL(IDX.IndexName,N''),
    IndexSort=ISNULL(IDX.Sort,N''),
    Create_Date=O.Create_Date,
    Modify_Date=O.Modify_date
FROM sys.columns C
    INNER JOIN sys.objects O
        ON C.[object_id]=O.[object_id]
            AND O.type='U'
            AND O.is_ms_shipped=0
    INNER JOIN sys.types T
        ON C.user_type_id=T.user_type_id
    LEFT JOIN sys.default_constraints D
        ON C.[object_id]=D.parent_object_id
            AND C.column_id=D.parent_column_id
            AND C.default_object_id=D.[object_id]
    LEFT JOIN sys.extended_properties PFD
        ON PFD.class=1 
            AND C.[object_id]=PFD.major_id 
            AND C.column_id=PFD.minor_id
--             AND PFD.name='Caption'  -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
    LEFT JOIN sys.extended_properties PTB
        ON PTB.class=1 
            AND PTB.minor_id=0 
            AND C.[object_id]=PTB.major_id
--             AND PFD.name='Caption'  -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) 
    LEFT JOIN                       -- 索引及主键信息
    (
        SELECT 
            IDXC.[object_id],
            IDXC.column_id,
            Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
                WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
            PrimaryKey=IDX.is_primary_key,
            IndexName=IDX.Name
        FROM sys.indexes IDX
        INNER JOIN sys.index_columns IDXC
            ON IDX.[object_id]=IDXC.[object_id]
                AND IDX.index_id=IDXC.index_id
        LEFT JOIN sys.key_constraints KC
            ON IDX.[object_id]=KC.[parent_object_id]
                AND IDX.index_id=KC.unique_index_id
        INNER JOIN  -- 对于一个列包含多个索引的情况,只显示第1个索引信息
        (
            SELECT [object_id], Column_id, index_id=MIN(index_id)
            FROM sys.index_columns
            GROUP BY [object_id], Column_id
        ) IDXCUQ
            ON IDXC.[object_id]=IDXCUQ.[object_id]
                AND IDXC.Column_id=IDXCUQ.Column_id
                AND IDXC.index_id=IDXCUQ.index_id
    ) IDX
        ON C.[object_id]=IDX.[object_id]
            AND C.column_id=IDX.column_id 
WHERE O.name=N'" + sTableName + "' ORDER BY O.name,C.column_id ";
            // 获取字段
            #endregion 

            string connstr = "Data Source={0};DataBase={1};Password={2};User ID={3}";
            SqlConnection _sqlcon = new SqlConnection(string.Format(connstr, ProjectInfo.Server, dataBaseName, ProjectInfo.Pwd, ProjectInfo.UID));
            DataTable dtColumn = new DataTable("Columns");
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(strSql, _sqlcon);
            da.Fill(dtColumn);

            List<ETableInfo> list = new List<ETableInfo>();

            foreach (DataRow row in dtColumn.Rows)
            {
                ETableInfo entity = new ETableInfo();

                entity.ColumnName = row["ColumnName"].ToString();
                entity.ColumnID = int.Parse(row["ColumnID"].ToString());
                entity.SystemTypeName = row["SystemTypeName"].ToString();
                entity.Precision = int.Parse(row["Precision"].ToString());
                entity.Scale = int.Parse(row["Scale"].ToString());
                entity.MaxLength = int.Parse(row["Length"].ToString());
                entity.Nullable = (bool)row["Nullable"];
                entity.Computed = (bool)row["Computed"];
                entity.Identity = (bool)row["Identity"];
                entity.Description = row["Description"].ToString() ?? "";
                entity.Default = row["Default"].ToString();
                entity.PrimaryKey = (bool)row["PrimaryKey"];
                entity.Remark = string.IsNullOrEmpty(row["Description"].ToString()) == true ? entity.Remark = row["ColumnName"].ToString() : row["Description"].ToString().Replace("\r\n", "");
                entity.Type = GetLanguageTypeName(entity.SystemTypeName);
                entity.DbType = GetDbTypeName(entity.SystemTypeName);
                list.Add(entity);

            }
            _sqlcon.Close();
            _sqlcon.Dispose();
            return list;
        }

        public string Building(BuilderType type)
        {
            string retVal = "";
            switch (type)
            {
                case BuilderType.Entity:
                    retVal = BuilderEntity();
                    break;
                case BuilderType.IRepository:
                    retVal = BuilderIRepository();
                    break;
                case BuilderType.IService:
                    retVal = BuilderIService();
                    break;
                case BuilderType.Mapping:
                    retVal = BuilderMap();
                    break;
                case BuilderType.Repository:
                    retVal = BuilderRepository();
                    break;
                case BuilderType.Service:
                    retVal = BuilderService();
                    break;
                case BuilderType.ServiceModel:
                    retVal = BuilderServiceModel();
                    break;
            }
            return retVal;
        }

        #region Building
        private string BuilderEntity()
        {


            //从文件中读取模板
            Template template = velocity.GetTemplate("Template/TemplateFile/IBatis/DataEntity.vm");
            context.Put("list", ProjectInfo);

            //合并模板
            StringWriter writer = new StringWriter();
            template.Merge(context, writer);

            DirectoryInfo dic = new DirectoryInfo(ProjectInfo.EntitySavePath);
            dic.Create();

            using (StreamWriter writerFile = new StreamWriter(ProjectInfo.EntitySavePath + "/E" + ProjectInfo.TableName + ".cs", false, Encoding.UTF8, 200))
            {
                writerFile.Write(writer);
                writerFile.Flush();
                writerFile.Close();
            }

            return writer.ToString();
        }

        private string BuilderMap()
        {
            //从文件中读取模板
            Template template = velocity.GetTemplate("Template/TemplateFile/IBatis/Mapping.vm");
            context.Put("list", ProjectInfo);
            //合并模板
            StringWriter writer = new StringWriter();
            template.Merge(context, writer);

            DirectoryInfo dic = new DirectoryInfo(ProjectInfo.MapXmlSavePath);
            dic.Create();

            using (StreamWriter writerFile = new StreamWriter(ProjectInfo.MapXmlSavePath + "/" + ProjectInfo.TableName + ".xml", false, Encoding.UTF8, 200))
            {
                writerFile.Write(writer);
                writerFile.Flush();
                writerFile.Close();
            }



            return writer.ToString();
        }

        private string BuilderIRepository()
        {

            //从文件中读取模板
            Template template = velocity.GetTemplate("Template/TemplateFile/IBatis/IRepository.vm");
            context.Put("list", ProjectInfo);
            context.Put("StringHelper", new StringHelper());
            //合并模板
            StringWriter writer = new StringWriter();
            template.Merge(context, writer);

            DirectoryInfo dic = new DirectoryInfo(ProjectInfo.IRepositorySavePath);
            dic.Create();

            using (StreamWriter writerFile = new StreamWriter(ProjectInfo.IRepositorySavePath + "/I" + ProjectInfo.TableName + ".cs", false, Encoding.UTF8, 200))
            {
                writerFile.Write(writer);
                writerFile.Flush();
                writerFile.Close();
            }


            return writer.ToString();
        }

        private string BuilderIService()
        {

            //从文件中读取模板
            Template template = velocity.GetTemplate("Template/TemplateFile/IBatis/IService.vm");
            context.Put("list", ProjectInfo);
            context.Put("StringHelper", new StringHelper());
            //合并模板
            StringWriter writer = new StringWriter();
            template.Merge(context, writer);

            DirectoryInfo dic = new DirectoryInfo(ProjectInfo.IServiceSavePath);
            dic.Create();

            using (StreamWriter writerFile = new StreamWriter(ProjectInfo.IServiceSavePath + "/I" + ProjectInfo.TableName + "Service.cs", false, Encoding.UTF8, 200))
            {
                writerFile.Write(writer);
                writerFile.Flush();
                writerFile.Close();
            }


            return writer.ToString();
        }

        private string BuilderService()
        {

            //从文件中读取模板
            Template template = velocity.GetTemplate("Template/TemplateFile/IBatis/Service.vm");
            context.Put("list", ProjectInfo);
            context.Put("StringHelper", new StringHelper());
            //合并模板
            StringWriter writer = new StringWriter();
            template.Merge(context, writer);

            DirectoryInfo dic = new DirectoryInfo(ProjectInfo.ServiceSavePath);
            dic.Create();

            using (StreamWriter writerFile = new StreamWriter(ProjectInfo.ServiceSavePath + "/" + ProjectInfo.TableName + "Service.cs", false, Encoding.UTF8, 200))
            {
                writerFile.Write(writer);
                writerFile.Flush();
                writerFile.Close();
            }


            return writer.ToString();
        }

        private string BuilderRepository()
        {
            //从文件中读取模板
            Template template = velocity.GetTemplate("Template/TemplateFile/IBatis/Repository.vm");
            context.Put("list", ProjectInfo);
            context.Put("StringHelper", new StringHelper());
            //合并模板
            StringWriter writer = new StringWriter();
            template.Merge(context, writer);

            DirectoryInfo dic = new DirectoryInfo(ProjectInfo.RepositorySavePath);
            dic.Create();

            using (StreamWriter writerFile = new StreamWriter(ProjectInfo.RepositorySavePath + "/" + ProjectInfo.TableName + "Repository.cs", false, Encoding.UTF8, 200))
            {
                writerFile.Write(writer);
                writerFile.Flush();
                writerFile.Close();
            }


            return writer.ToString();
        }

        private string BuilderServiceModel()
        {


            //从文件中读取模板
            Template template = velocity.GetTemplate("Template/TemplateFile/IBatis/ServiceModel.vm");
            context.Put("list", ProjectInfo);

            //合并模板
            StringWriter writer = new StringWriter();
            template.Merge(context, writer);

            DirectoryInfo dic = new DirectoryInfo(ProjectInfo.ServiceModelSavePath);
            dic.Create();

            using (StreamWriter writerFile = new StreamWriter(ProjectInfo.ServiceModelSavePath + "/" + ProjectInfo.TableName + "Model.cs", false, Encoding.UTF8, 200))
            {
                writerFile.Write(writer);
                writerFile.Flush();
                writerFile.Close();
            }

            return writer.ToString();
        }

        private string BuilderEntityToModel()
        {
            //从文件中读取模板
            Template template = velocity.GetTemplate("Template/TemplateFile/IBatis/EntityToModel.vm");
            context.Put("list", ProjectInfo);

            //合并模板
            StringWriter writer = new StringWriter();
            template.Merge(context, writer);

            return writer.ToString();

        }

        private string BuilderModelToEntity()
        {
            //从文件中读取模板
            Template template = velocity.GetTemplate("Template/TemplateFile/IBatis/ModelToEntity.vm");
            context.Put("list", ProjectInfo);

            //合并模板
            StringWriter writer = new StringWriter();
            template.Merge(context, writer);

            return writer.ToString();

        }

        #endregion
    }
}
